﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.Linq;
using DTO;

namespace BLL
{
    public partial class ExcelFunc
    {
        private readonly string _sConnPath = "Provider=Microsoft.{0}.OLEDB.{1};Data Source={2};Extended Properties=\"Excel {3};HDR=YES\"";
        public static OnReadProgress ReadProgress;
        public delegate void OnReadProgress(int progess);
        public static OnLoadComplete LoadDataToUI;
        public delegate void OnLoadComplete(DataTable dt);
        private static String[] _sExcelSheetName;
        private static DataTable _dtRaw;


        public DataTable dtRaw
        {
            get { return _dtRaw; }
            set { _dtRaw = value; }
        }       

        public String[] sSheetName
        {
            get { return _sExcelSheetName; }
        }

        private DataTable ParseToDataTable(string sFilePath)
        {
            System.IO.FileInfo fi = new System.IO.FileInfo(sFilePath);
            string sConnPath = null;
            //For Excel 93-2000
            if (fi.Extension.Equals(".xls"))
            {
                // For Excel Below 2007 Format
                sConnPath = string.Format(_sConnPath,
                           "Jet", "4.0", sFilePath, "8.0");
            }
            //For Excel 2007-2010
            else if (fi.Extension.Equals(".xlsx"))
            {
                // For Excel 2007 File  Format
                sConnPath = string.Format(_sConnPath,
                           "Ace", "12.0", sFilePath, "12.0");
            }
            OleDbConnection conn = new OleDbConnection(sConnPath);
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                return dt;
            }
            catch
            {                
                dt.Dispose();
                return null;
            }
            finally
            {
                //free resource
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
                sConnPath = null;
            }
        }

        public void sGetExcelSheetNames(string sFilePath, ref string message)
        {
            try
            {
                //Retrieve Data from Excel
                DataTable dt = new DataTable();
                dt = ParseToDataTable(sFilePath);
                String[] excelSheets = new String[dt.Rows.Count];
                try
                {
                    if (dt == null)
                    {
                        return;
                    }
                    //Loop through it to get sheet's names
                    int i = 0;
                    foreach (DataRow row in dt.Rows)
                    {
                        if (!row["TABLE_NAME"].ToString().Contains("Database"))
                        {
                            excelSheets[i] = row["TABLE_NAME"].ToString().Replace("$", "").Replace("'", "");
                            i++;
                        }
                    }

                    _sExcelSheetName = excelSheets;
                }
                catch
                {
                    _sExcelSheetName = null;
                    return;
                }
                excelSheets = null;
            }
            catch
            {
                message = "File nhập vào không hợp lệ. Xin hãy thử lại.";
                return;
            }
        }

        public void ImportData(string sFilePath, string sSheetName, ref string message)
        {
            string sConnPath = null;
            
            System.IO.FileInfo fi = new System.IO.FileInfo(sFilePath);
            if (fi.Exists)
            {
                if (fi.Extension.Equals(".xls"))
                {
                    // For Excel Below 2007 Format
                    sConnPath = string.Format(_sConnPath,
                               "Jet", "4.0", sFilePath, "8.0");
                }
                else if (fi.Extension.Equals(".xlsx"))
                {
                    // For Excel 2007 File  Format
                    sConnPath = string.Format(_sConnPath,
                               "Ace", "12.0", sFilePath, "12.0");
                }
            }
            else
            {
                message = "File không tồn tại. Xin hãy xem lại đường dẫn nhập vào.";
            }

            OleDbConnection conn = new OleDbConnection(sConnPath);
            DataTable dt = new DataTable();
            try
            {
                conn.Open();                
                OleDbDataAdapter dtAdapter = new OleDbDataAdapter("Select * from [" + sSheetName + "$]", conn);
                dtAdapter.Fill(dt);
                ReadProgress(60);
                if (!IsColNameRight(dt))
                {
                    message = "Tên cột không đúng định dạng.";
                }
            }
            catch(Exception ex)
            {
                message = ex.ToString();
                dt.Dispose();
                return;
            }
            finally
            {
                if (message == "")
                {
                    //Change Col Name to match with Database
                    ChangeColName(dt);
                    ReadProgress(70);
                    //Remove all null rows
                    for (int i = dt.Rows.Count - 1; i >= 0; i--)
                    {
                        bool allNull = true;
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            if (dt.Rows[i][j] != DBNull.Value)
                            {
                                allNull = false;
                            }
                        }
                        if (allNull)
                        {
                            dt.Rows[i].Delete();
                        }
                    }
                    //accept modification in datatable
                    dt.AcceptChanges();
                    ReadProgress(80);
                    //Check
                    CheckBrandCodeInTable(dt);
                    CheckCollectorCodeInTable(dt);
                    CheckColPhoneInTable(dt);
                    //Convert From Code to ID
                    ConvertBrandCodeToId(dt);
                    ConvertCollectorCodeToId(dt);
                    //Add EDD
                    AddEDD(dt);                    
                    //Save to Storage                    
                    _dtRaw = dt.Copy();
                    ReadProgress(90);
                    //Transfer data to gridview on UI
                    LoadDataToUI(dt);
                    ReadProgress(100);
                }
                //free resource
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }

        private void ChangeColName(DataTable dt)
        { 
            dt.Columns[0].ColumnName = "SourceName";
                    dt.Columns[1].ColumnName = "CollectedDate";
                    dt.Columns[2].ColumnName = "Collector_ID";
                    dt.Columns[3].ColumnName = "ClinicInfo";
                    dt.Columns[4].ColumnName = "LastName";
                    dt.Columns[5].ColumnName = "FirstName";
                    dt.Columns[6].ColumnName = "HomePhoneNumber";
                    dt.Columns[7].ColumnName = "OfficePhoneNumber";
                    dt.Columns[8].ColumnName = "CellPhoneNumber";
                    dt.Columns[9].ColumnName = "HomeNumber";
                    dt.Columns[10].ColumnName = "Substreet";
                    dt.Columns[11].ColumnName = "Street";
                    dt.Columns[12].ColumnName = "Ward_Code";
                    dt.Columns[13].ColumnName = "District_Code";
                    dt.Columns[14].ColumnName = "Province_Code";
                    dt.Columns[15].ColumnName = "EDDW";
                    dt.Columns[16].ColumnName = "EDD";
                    dt.Columns[17].ColumnName = "CurrentBrandName";
                    dt.Columns[18].ColumnName = "PreviousBrandName";
        }

        private void AddEDD(DataTable dt)
        {            
            // Add column EDDS
            dt.Columns.Add("EDDS");
            //Add EDD, EDDW, EDDS to datatable
            foreach (DataRow row in dt.Rows)
            {
                if (!string.IsNullOrEmpty(row["EDDW"].ToString()))
                {
                    row["EDD"] = HelperClass.GetEDD(int.Parse(row["EDDW"].ToString().Split('W')[0].ToString()));
                }
                if (!string.IsNullOrEmpty(row["EDD"].ToString()))
                {
                    row["EDDW"] = HelperClass.GetEDDWeek(DateTime.Parse(row["EDD"].ToString())).ToString() + "W";
                    row["EDDS"] = HelperClass.GetEDDS(DateTime.Parse(row["EDD"].ToString()));
                }
                else
                {
                    row["EDDS"] = "BLANK";
                }      
            }
        }

        private void ConvertBrandCodeToId(DataTable table)
        {
            DataTable dtttmp = new DataTable();
            dtttmp = table.Clone();
            foreach (DataRow row in table.Rows)
            { 
                string currentbrand = row["CurrentBrandName"].ToString().Trim();
                string previousbrand = row["PreviousBrandName"].ToString().Trim();
                    if (!string.IsNullOrWhiteSpace(currentbrand))
                        row["CurrentBrandName"] = SharedData.BrandList.Where(c => c.Brand_Code == currentbrand).SingleOrDefault().Brand_ID;
                    else
                        row["CurrentBrandName"] = SharedData.BrandList.Where(c => c.Brand_Code == "NONE").SingleOrDefault().Brand_ID;
                    if (!string.IsNullOrWhiteSpace(previousbrand))
                        row["PreviousBrandName"] = SharedData.BrandList.Where(c => c.Brand_Code == previousbrand).SingleOrDefault().Brand_ID;
                    else
                        row["PreviousBrandName"] = SharedData.BrandList.Where(c => c.Brand_Code == "NONE").SingleOrDefault().Brand_ID;
            }
            table.AcceptChanges();
        }

        private void ConvertCollectorCodeToId(DataTable table)
        {
            DataTable dttmp = new DataTable();
            dttmp = table.Clone();
            foreach (DataRow row in table.Rows)
            {
                string collectorcode = row["Collector_ID"].ToString().Trim(); 
                row["Collector_ID"] = SharedData.CollectorList.Where(a => a.Collector_Code == collectorcode).SingleOrDefault().Collector_ID;
            }
            table.AcceptChanges();
        }

    }
}
